package qimao.com.edu.test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import qimao.com.edu.dao.BaseDao;
import qimao.com.edu.entity.CategoryMinor;

public class JPAtest {
	public void createCategoryWithParent() {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    
	    try {
	        // 使用BaseDao获取数据库连接
	        con = BaseDao.getCon();
	        
	        // 开始事务
	        con.setAutoCommit(false);
	        
	        // 1. 创建父分类
	        String parentSql = "INSERT INTO category_major (name) VALUES (?)";
	        ps = con.prepareStatement(parentSql, Statement.RETURN_GENERATED_KEYS);
	        ps.setString(1, "文学");
	        ps.executeUpdate();
	        
	        // 获取生成的父分类ID
	        rs = ps.getGeneratedKeys();
	        Long parentId = null;
	        if (rs.next()) {
	            parentId = rs.getLong(1);
	        }
	        rs.close();
	        ps.close();
	        
	        // 2. 创建子分类并关联到父分类
	        String childSql = "INSERT INTO category_minor (minor_name, parent_id) VALUES (?, ?)";
	        ps = con.prepareStatement(childSql);
	        ps.setString(1, "武侠小说");
	        ps.setLong(2, parentId);
	        ps.executeUpdate();
	        
	        // 提交事务
	        con.commit();
	        
	    } catch (Exception e) {
	        // 发生异常时回滚事务
	        if (con != null) {
	            try {
	                con.rollback();
	            } catch (SQLException ex) {
	                ex.printStackTrace();
	            }
	        }
	        e.printStackTrace();
	    } finally {
	        // 关闭资源
	        BaseDao.closeAll(con, ps, rs);
	    }
	}

	
	/**
	 * 删除分类及其关联关系
	 * @param categoryId 要删除的分类ID
	 * @param isParent 是否为父分类
	 */
	public void deleteCategory(Long categoryId, boolean isParent) {
	    Connection con = null;
	    PreparedStatement pstmt = null;
	    
	    try {
	        // 获取数据库连接
	        con = BaseDao.getCon();
	        
	        // 开始事务
	        con.setAutoCommit(false);
	        
	        if (isParent) {
	            // 1. 如果是父分类，先删除所有子分类
	            String deleteChildrenSql = "DELETE FROM category_minor WHERE parent_id = ?";
	            pstmt = con.prepareStatement(deleteChildrenSql);
	            pstmt.setLong(1, categoryId);
	            pstmt.executeUpdate();
	            pstmt.close();
	            
	            // 2. 然后删除父分类
	            String deleteParentSql = "DELETE FROM category_major WHERE id = ?";
	            pstmt = con.prepareStatement(deleteParentSql);
	            pstmt.setLong(1, categoryId);
	            pstmt.executeUpdate();
	        } else {
	            // 如果是子分类，直接删除
	            String deleteChildSql = "DELETE FROM category_minor WHERE id = ?";
	            pstmt = con.prepareStatement(deleteChildSql);
	            pstmt.setLong(1, categoryId);
	            pstmt.executeUpdate();
	        }
	        
	        // 提交事务
	        con.commit();
	        
	    } catch (Exception e) {
	        // 发生异常时回滚事务
	        if (con != null) {
	            try {
	                con.rollback();
	            } catch (SQLException ex) {
	                ex.printStackTrace();
	            }
	        }
	        e.printStackTrace();
	    } finally {
	        // 关闭资源
	        BaseDao.closeAll(con, pstmt, null);
	    }
	}

}
